﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using smp.QueryDb.Structure;

namespace smp.QueryDb.Tools
{
    public class SqlStructureGenerator : IDbStructureGenerator
    {
        #region Implementation of IDbStructureGenerator

        public string GetClassStringForAllAllObjects(string connectionString, string classNamespace)
        {
            using(var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var databaseName = connection.Database;
                var tables = GetTableColumns(connection);
                var procs = GetProcedures(connection);
                return GetCodeStringForAllObjects(tables, procs, classNamespace, databaseName);
            }
        }

        public void WriteDatabaseFileToProject(string connectionString, string classNamespace, string projectFilename, string filePath, string fileName)
        {
            var fileContent = GetClassStringForAllAllObjects(connectionString, classNamespace);

            if (!fileName.EndsWith(".cs"))
            {
                fileName += ".cs";
            }
            
            using (var outfile = new StreamWriter(filePath + "\\" + fileName))
            {
                outfile.Write(fileContent);
            }

            var contents =
                File.ReadAllLines(projectFilename).ToList();

            var foundFiles = false;
            var insertIndex = 0;
            for (int i = 0; i < contents.Count; i++)
            {
                var line = contents[i];
                if (line.Contains("Compile Include"))
                {
                    foundFiles = true;
                }
                if (foundFiles && line.Contains(fileName))
                {
                    break;
                }
                if (foundFiles && line.Contains("</ItemGroup>"))
                {
                    insertIndex = i;
                    break;
                }
            }
            if(insertIndex > 0)
            {
                contents.Insert(insertIndex, "\t<Compile Include=\"" + fileName + "\" />");
                File.WriteAllLines(projectFilename, contents.ToArray());
            }
        }

        public string GetCodeStringForAllObjects(Dictionary<string, Table> tables, Dictionary<string, Procedure> procs, string namespaceName, string databaseName)
        {
            var text = new StringBuilder();
            text.AppendLine("using System.Data;");
            text.AppendLine("using smp.QueryDb.Structure;");
            text.AppendLine("");
            text.AppendLine("namespace " + namespaceName);
            text.AppendLine("{");
            text.AppendLine("\t#region database class");
            text.AppendLine("");
            text.AppendLine("\tpublic class " + databaseName);
            text.AppendLine("\t{");


            foreach (var table in tables)
            {
                text.AppendLine(string.Format("\t\tpublic static {0} {0} = new {0}();", table.Key));
            }
            foreach (var proc in procs)
            {
                text.AppendLine(string.Format("\t\tpublic static {0} {0} = new {0}();", proc.Key));
            }
            text.AppendLine("\t}");
            text.AppendLine("");
            text.AppendLine("\t#endregion");
            text.AppendLine("");

            text.AppendLine("\t#region Tables and Views");
            text.AppendLine("");

            foreach (var dbTable in tables)
            {
                text.AppendLine("\tpublic class " + dbTable.Key + " : " + (dbTable.Value.IsReadOnly ? "View" : "Table"));
                text.AppendLine("\t{");
                text.AppendLine("\t\t public " + dbTable.Key + "() : base(\"" + dbTable.Value.Database + "\", \"" + dbTable.Value.Schema + "\", \"" + dbTable.Key + "\")");

                text.AppendLine("\t\t{");
                if (dbTable.Value.Id != null)
                {
                    text.AppendLine(string.Format("\t\t\t{0} = SetId(\"{0}\", DbType.{1}, IdGenerationType.{2});",
                                                  dbTable.Value.Id.Name,
                                                  dbTable.Value.Id.DataType.ToString(),
                                                  dbTable.Value.Id.IdGenerationType.ToString()));
                }
                foreach (var column in dbTable.Value.Columns)
                {
                    if(column.FieldLength.HasValue)
                    {
                        text.AppendLine(string.Format("\t\t\t{0} = AddColumn(\"{0}\", DbType.{1}, {2}, {3});", column.Name, column.DataType, column.IsNullable.ToString().ToLower(), column.FieldLength.Value));
                    }
                    else
                    {
                        text.AppendLine(string.Format("\t\t\t{0} = AddColumn(\"{0}\", DbType.{1}, {2});", column.Name, column.DataType, column.IsNullable.ToString().ToLower()));
                    }
                }
                text.AppendLine("\t\t}");

                if (dbTable.Value.Id != null)
                {
                    text.AppendLine("\t\tpublic static Id " + dbTable.Value.Id.Name + ";");
                }
                foreach (var column in dbTable.Value.Columns)
                {
                    text.AppendLine("\t\tpublic static Column " + column.Name + ";");
                }

                text.AppendLine("\t}");
                text.AppendLine("");
            }
            text.AppendLine("\t#endregion");
            text.AppendLine("");


            text.AppendLine("\t#region Procedures");
            text.AppendLine("");

            foreach (var dbproc in procs)
            {
                text.AppendLine("\tpublic class " + dbproc.Key + " : Procedure");
                text.AppendLine("\t{");
                text.AppendLine("\t\t public " + dbproc.Key + "() : base(\"" + dbproc.Value.Database + "\", \"" + dbproc.Value.Schema + "\", \"" + dbproc.Key + "\")");

                text.AppendLine("\t\t{");
                foreach (var parameter in dbproc.Value.Parameters)
                {
                    text.AppendLine(string.Format("\t\t\t{0} = AddParameter(\"{0}\", DbType.{1}, ParameterDirection.{2});", parameter.Name, parameter.DataType, parameter.Direction));
                }
                text.AppendLine("\t\t}");

                foreach (var parameter in dbproc.Value.Parameters)
                {
                    text.AppendLine("\t\tpublic static ProcedureParameter " + parameter.Name + ";");
                }

                text.AppendLine("\t}");
                text.AppendLine("");
            }

            text.AppendLine("\t#endregion");
            text.AppendLine("");

            text.AppendLine("}");
            return text.ToString();
        }

        public Dictionary<string, Table> GetTableColumns(SqlConnection connection)
        {
            var sql =
                @"SELECT 
                          c.TABLE_NAME TableName,
                          c.COLUMN_NAME ColumnName, 
                          c.IS_NULLABLE IsNullable,
                          c.DATA_TYPE DataType,
                          (CASE WHEN EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND TABLE_NAME = c.TABLE_NAME AND COLUMN_NAME = c.COLUMN_NAME) THEN 'True'
                          ELSE 'False' END) IsPrimaryKey,
                          (CASE WHEN COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 THEN 'True'
                          ELSE 'False' END) IsIdentity,
                          (CASE WHEN EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = c.TABLE_NAME) THEN 'True'
                          ELSE 'False' END) IsView,
                          c.TABLE_SCHEMA TableSchema,
                          c.TABLE_CATALOG DatabaseName,
                          c.CHARACTER_MAXIMUM_LENGTH FieldLength
                    FROM INFORMATION_SCHEMA.COLUMNS c
                    ORDER BY c.TABLE_NAME, c.COLUMN_NAME";

            var tables = new Dictionary<string, Table>();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var tableName = reader["TableName"].ToString();
                        if (string.IsNullOrEmpty(tableName))
                            continue;

                        var schema = reader["TableSchema"].ToString();
                        if (string.IsNullOrEmpty(schema))
                            continue;

                        var database = reader["DatabaseName"].ToString();
                        if (string.IsNullOrEmpty(database))
                            continue;

                        if (!tables.ContainsKey(tableName))
                        {
                            tables.Add(tableName, new Table(database, schema, tableName));
                        }

                        var isView = reader["IsView"].ToString();
                        tables[tableName].IsReadOnly = isView == "True";

                        var columnName = reader["ColumnName"].ToString();
                        if (string.IsNullOrEmpty(columnName))
                            continue;

                        var dataType = GetType(reader["DataType"].ToString());
                        if (!dataType.HasValue)
                            continue;

                        var table = tables[tableName];

                        var isId = reader["IsPrimaryKey"].ToString() == "True";
                        if (isId)
                        {
                            var isIdentity = reader["IsIdentity"].ToString() == "True";
                            tables[tableName].Id = new Id(table, columnName, dataType.Value, isIdentity);
                            continue;
                        }

                        var isNullable = reader["IsNullable"].ToString() == "YES";

                        var fieldLengthString = reader["FieldLength"].ToString();
                        if(!string.IsNullOrEmpty(fieldLengthString) && dataType.Value == DbType.String)
                        {
                            int fieldLength;
                            if(int.TryParse(fieldLengthString, out fieldLength))
                            {
                                tables[tableName].Columns.Add(new Column(table, columnName, dataType.Value, isNullable, fieldLength));
                                continue;
                            }
                        }
                        tables[tableName].Columns.Add(new Column(table, columnName, dataType.Value, isNullable));
                    }
                }
            }
            return tables;
        }

        public Dictionary<string, Procedure> GetProcedures(SqlConnection connection)
        {
            var procedureDataTable = connection.GetSchema("Procedures");
            var procedureDataColumn = procedureDataTable.Columns["ROUTINE_NAME"];
            var procSchemaColumn = procedureDataTable.Columns["SPECIFIC_SCHEMA"];

            var procs = new Dictionary<string, Procedure>();

            if (procedureDataColumn != null)
            {
                foreach (DataRow row in procedureDataTable.Rows)
                {
                    var procedureName = row[procedureDataColumn].ToString();
                    var procedureSchema = row[procSchemaColumn].ToString();

                    if (!procs.ContainsKey(procedureName))
                    {
                        procs.Add(procedureName, new Procedure(connection.Database, procedureSchema, procedureName));
                    }

                    var parmsDataTable = connection.GetSchema("ProcedureParameters", new[] { null, null, procedureName });

                    foreach (DataRow parmRow in parmsDataTable.Rows)
                    {
                        var direction = GetDirection(parmRow[parmsDataTable.Columns["PARAMETER_MODE"]].ToString());
                        var name = parmRow[parmsDataTable.Columns["PARAMETER_NAME"]].ToString();
                        var dataType = GetType(parmRow[parmsDataTable.Columns["DATA_TYPE"]].ToString());

                        if (string.IsNullOrEmpty(name) || !dataType.HasValue || !direction.HasValue)
                            continue;
                        procs[procedureName].Parameters.Add(new ProcedureParameter(name, dataType.Value, direction.Value));
                    }
                }
            }

            return procs;
        }

        public string GenerateCreateTableScript(List<Table> tables)
        {
            var commandBuilder = new StringBuilder();
            foreach(var table in tables)
            {
                var schema = "dbo";
                if(string.IsNullOrEmpty(table.Schema))
                {
                    schema = table.Schema;
                }
                commandBuilder.AppendFormat("CREATE TABLE [{0}].[{1}] (", schema, table.Name);
                commandBuilder.AppendFormat("[{0}] {1} NOT NULL,", table.Id.Name, GetSqlType(table.Id.DataType));
                foreach(var column in table.Columns)
                {
                    commandBuilder.AppendFormat("[{0}] {1} {2},", column.Name, GetSqlType(column.DataType), column.IsNullable ? "NULL" : "NOT NULL");
                }
                if(commandBuilder[commandBuilder.Length -1] == ',')
                {
                    commandBuilder.Remove(commandBuilder.Length - 1, 1);
                }
                commandBuilder.Append(")");
                
                commandBuilder.AppendFormat(
                        "ALTER TABLE [{0}].[{1}] ADD CONSTRAINT [pk_{1}_{2}] PRIMARY KEY CLUSTERED  ([{2}]) ON [PRIMARY]",
                        table.Schema, table.Name, table.Id.Name);
            }
            return commandBuilder.ToString();
        }

        public string GenerateDropTableScript(List<Table> tables)
        {
            var commandBuilder = new StringBuilder();
            foreach (var table in tables)
            {
                var schema = "dbo";
                if (string.IsNullOrEmpty(table.Schema))
                {
                    schema = table.Schema;
                }
                commandBuilder.AppendFormat(" DROP TABLE [{0}].[{1}]", schema, table.Name);
            }
            return commandBuilder.ToString();
        }

        #endregion

        #region private methods

        private DbType? GetType(string sqlType)
        {
            SqlDbType sqlDbType;
            if (!Enum.TryParse(sqlType, true, out sqlDbType))
                return null;

            switch (sqlDbType)
            {
                case SqlDbType.BigInt:
                    return DbType.Int64;

                case SqlDbType.Binary:
                case SqlDbType.VarBinary:
                case SqlDbType.Image:
                case SqlDbType.Timestamp:
                    return DbType.Binary;

                case SqlDbType.Bit:
                    return DbType.Boolean;

                case SqlDbType.Char:
                    return DbType.AnsiStringFixedLength;

                case SqlDbType.DateTime:
                case SqlDbType.Date:
                case SqlDbType.SmallDateTime:
                    return DbType.DateTime;

                case SqlDbType.Decimal:
                case SqlDbType.Money:
                case SqlDbType.SmallMoney:
                    return DbType.Decimal;

                case SqlDbType.Float:
                    return DbType.Double;

                case SqlDbType.Int:
                    return DbType.Int32;

                case SqlDbType.NChar:
                    return DbType.StringFixedLength;

                case SqlDbType.Text:
                case SqlDbType.NVarChar:
                case SqlDbType.VarChar:
                case SqlDbType.NText:
                    return DbType.String;

                case SqlDbType.Real:
                    return DbType.Single;

                case SqlDbType.UniqueIdentifier:
                    return DbType.Guid;

                case SqlDbType.SmallInt:
                    return DbType.Int16;

                case SqlDbType.TinyInt:
                    return DbType.Byte;

                case SqlDbType.Variant:
                    return DbType.Object;

                case SqlDbType.Xml:
                    return DbType.Xml;

                case SqlDbType.Time:
                    return DbType.Time;

                case SqlDbType.DateTime2:
                    return DbType.DateTime2;

                case SqlDbType.DateTimeOffset:
                    return DbType.DateTimeOffset;

                default:
                    return null;
            }
        }

        private SqlDbType? GetSqlType(DbType dbType)
        {
            switch(dbType)
            {
                case DbType.AnsiString:
                    return SqlDbType.VarChar;
                case DbType.Binary:
                    return SqlDbType.Binary;
                case DbType.Byte:
                    return SqlDbType.TinyInt;
                case DbType.Boolean:
                    return SqlDbType.Bit;
                case DbType.Currency:
                    return SqlDbType.Money;
                case DbType.Date:
                    return SqlDbType.Date;
                case DbType.DateTime:
                    return SqlDbType.DateTime;
                case DbType.Decimal:
                    return SqlDbType.Decimal;
                case DbType.Double:
                    return SqlDbType.Float;
                case DbType.Guid:
                    return SqlDbType.UniqueIdentifier;
                case DbType.Int16:
                    return SqlDbType.SmallInt;
                case DbType.Int32:
                    return SqlDbType.Int;
                case DbType.Int64:
                    return SqlDbType.BigInt;
                case DbType.Object:
                    return SqlDbType.Variant;
                case DbType.String:
                    return SqlDbType.NVarChar;
                case DbType.Time:
                    return SqlDbType.Time;
                case DbType.UInt16:
                    return SqlDbType.SmallInt;
                case DbType.UInt32:
                    return SqlDbType.Int;
                case DbType.UInt64:
                    return SqlDbType.BigInt;
                case DbType.AnsiStringFixedLength:
                    return SqlDbType.Char;
                case DbType.StringFixedLength:
                    return SqlDbType.VarChar;
                case DbType.Xml:
                    return SqlDbType.Xml;
                case DbType.DateTime2:
                    return SqlDbType.DateTime2;
                case DbType.DateTimeOffset:
                    return SqlDbType.DateTimeOffset;
                case DbType.SByte:
                case DbType.Single:
                case DbType.VarNumeric:
                default:
                    return null;
            }
        }

        private ParameterDirection? GetDirection(string sqlDirection)
        {
            if (sqlDirection.ToLower() == "in")
                return ParameterDirection.Input;
            if (sqlDirection.ToLower() == "out")
                return ParameterDirection.Output;
            if (sqlDirection.ToLower() == "inout")
                return ParameterDirection.InputOutput;
            return null;
        }

        #endregion
    }
}
